<html>
<head>
<title>Tutorial 4 - The Late Returns Summary report</title>
<link rel="stylesheet" type="text/css" href="../media/style.css">
</head>
<body>

<table border="0" cellspacing="0" cellpadding="0" height="48" width="100%">
  <tr>
    <td width="100%">
			<table width="100%" height="78" border="0" align="left" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" id="topnav">
			<tr>
				<!-- Header Side -->
				<td width="17">
					<img height="78" src="media/reportico100.png"/>
				</td>
				<td align="right"></td>
				<td align="center">
					<table width="100%" height="60" border="0" align="left" cellpadding="0" cellspacing="0">
						<tr>
							<td valign="top" align="left">
							<img src="media/repbanner.png"/></td>
							</td>
						</tr>
						<tr>
							<td align="center" valign="bottom" class="topmenucell">
								<nobr>
								&nbsp;
								</nobr>
							</td>
						</tr>
					</table>
				</td>
				<td width="103" class="toplogincell">
					&nbsp;
				</td>
			</tr>
			</table>
</td>
  </tr>
  <!--tr>
    <td class="header_top">reportico</td>
  </tr-->
  <tr><td class="header_line"><img src="../media/empty.png" width="1" height="1" border="0" alt=""  /></td></tr>
  <tr>
    <td class="header_menu">
  		  [ <a href="../classtrees_reportico.html" class="menu">class tree: reportico</a> ]
		  [ <a href="../elementindex_reportico.html" class="menu">index: reportico</a> ]
		  [ <a href="../elementindex.html" class="menu">all elements</a> ]
    </td>
  </tr>
  <tr><td class="header_line"><img src="../media/empty.png" width="1" height="1" border="0" alt=""  /></td></tr>
</table>

<table width="100%" border="0" cellpadding="0" cellspacing="0">
  <tr valign="top">
    <td width="200" class="menu">
      <b>Packages:</b><br />
              <a href="../li_reportico.html">reportico</a><br />
            <br /><br />
		<b>Tutorials/Manuals:</b><br />
					<strong>Package-level:</strong>
							<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.pkg.html">Reportico Tutorial And User Manual</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.manual.pkg.html">Reportico User Manual</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.quickstart.pkg.html">Reportico Installation</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.gettingstarted.pkg.html">Getting Started</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.structure.pkg.html">Reportico Installation Structure</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.using.pkg.html">Configuring Reportico - System Parameters</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.embedding.pkg.html">Embedding Reportico</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.security.pkg.html">Reportico Security</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.language.pkg.html">Language Support</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.projects.pkg.html">Projects</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.importlink.pkg.html">Importing and linking from reports</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.menus.pkg.html">Report Menus</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.prepare.pkg.html">Report Preparation and Execution</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.design.pkg.html">Report Design Mode</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.format.pkg.html">The Design Format Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.customcode.pkg.html">Custom Source Code</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.qrydet.pkg.html">The Query Details Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.assign.pkg.html">The Assignments Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.criteria.pkg.html">The Criteria Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.output.pkg.html">The Output Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.webservices.pkg.html">Turning Reports into Web Services (currently unavailable)</a>
</ul>
</li></ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.faq.pkg.html">Reportico FAQ</a>
</ul>
</li></ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.howto.pkg.html">Reportico Tutorial</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1.pkg.html">Tutorial No.1 - The Film Listing Report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_1.pkg.html">Tutorial 1 Stage 1 - Creating a basic report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_2.pkg.html">Tutorial 1 Stage 2 - Creating User Entry Criteria</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_3.pkg.html">Tutorial 1 Stage 3 - Working with expressions using assignments</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_4.pkg.html">Tutorial 1 Stage 4 - Working with groups</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_5.pkg.html">Tutorial 1 Stage 5 - Including database graphics into the report and generating drilldowns</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial2_1.pkg.html">Tutorial 2 - Creating the loan history report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial3_1.pkg.html">Tutorial 3 - The Monthly Returns Report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial4_1.pkg.html">Tutorial 4 - The Late Returns Summary report</a>
</ul>
</li></ul>
</li></ul>

							<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.appendix1.pkg.html">Appendix 1 - Reportico Project Configuration parameters</a>
</ul>

							<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.appendix2.pkg.html">Appendix 2 - Reportico URL request parameters</a>
</ul>

									                        <b>Files:</b><br />
      	  <div class="package">
			<a href="../reportico/_reportico.php.html">		reportico.php
		</a><br>
	  </div><br />
      
                </td>
    <td>
      <table cellpadding="10" cellspacing="0" width="100%" border="0"><tr><td valign="top">

<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="10%" align="left" valign="bottom"><a href=
"../reportico/tutorial_reportico.tutorial3_1.pkg.html">Prev</a></td>
<td width="80%" align="center" valign="bottom"></td>
<td width="10%" align="right" valign="bottom"><a href=
"../reportico/tutorial_reportico.appendix1.pkg.html">Next</a></td>
</tr>
</table>
<span><a name="installation."></a><h2 class="title">Tutorial 4 - The Late Returns Summary report</h2><p><strong class = "bold">Note that this tutorial requires you to enter custom source using PHP code. Custom code may not be
      entered when running in SAFE mode as the user could create SQL
      statements here that could delete and update data in the
      database or perform disk operations. By default, the tutorial projects have SAFE mode turned on. To continue with this tutorial you need to turn off SAFE mode by folowing the instructions on how to turn on and off Safe Mode in the <a href="../reportico/tutorial_reportico.security.pkg.html">Reportico Security</a> section.</strong></p>
In this tutorial you will
    <p><ol><li><p>Perform data manipulation prior to reporting using PHP custom code</p></li>
	<li><p>Add a pie chart as a final report summary</p></li></ol></p>
  <a name="tut4_run"></a><h3 class="title">Beginning this tutorial</h3><p>Select the <strong>Late Returns Summary - Begin the Tutorial</strong> from the tutorials menu (Alternatively you can point your browser at the URL <strong>http://{SERVER_ADDRESS}/{REPORTICO_INSTALL_DIRECTORY}/run.php?project=tutorials&amp;execute_mode=PREPARE&amp;xmlin=tut4_1_lateness.xml</strong>). You are now ready to start this tutorial.</p>
   <p>On entry to this tutorial, a date range criteria has already been set up to allow us to report on returns made between specified dates - the date range defaults to the period covering last month.</p>
   <p>In the next stage you will add some PHP code and the main report query.</p><hr />
   <a name="tut4_1_group"></a><h3 class="title">Add the custom PHP code</h3><p>In this report we want to show how many films have been returned (within the specified date period) more than 1 day late, and provide for counts for those that are 2 days late, 3 days late, 4 days late etc. In order to do this we are going to add some PHP code that will generate a temporary table containing the number of days late each loan was taken out for, and then set up the data query to select this date by number of days late.</p>
   <p>Enter design mode, click on the  <strong>Format</strong> tab and locate the <strong>Custom Code</strong> parameter. Enter the following PHP code :-
<code><pre>
    // Extract Users Date Range
    $fromdate =      $_criteria[&quot;date_range&quot;]-&gt;get_criteria_value(&quot;RANGE1&quot;);
    $todate = $_criteria[&quot;date_range&quot;]-&gt;get_criteria_value(&quot;RANGE2&quot;);

    // Create temporary table to hold number of days 
    // late a film was returned
    $sql =&quot;CREATE TEMPORARY TABLE t_lateness 
          ( 
              days_loaned INTEGER
           ) &quot;;
    $stat = $_pdo-&gt;query($sql); 

    if ( !$stat ) 
    {
	$err = $_pdo-&gt;errorInfo();
        trigger_error(&quot;Query Failed&lt;BR&gt;&quot;.$sql.&quot;&lt;br&gt;&quot; . 
              &quot;Error &quot;.$_pdo-&gt;errorCode(). &quot; - &quot;. 
              $err[2], E_USER_ERROR); 
              return; 
    } 

    // Now populate table with number of days late each film was 
    // returned
    $sql = &quot;INSERT INTO t_lateness 
            SELECT to_days(return_date) - to_days(loan_date)  
              FROM reptut_loan 
             WHERE return_date IS NOT NULL
               AND return_date BETWEEN $fromdate AND $todate
           &quot;;                                 
    $stat = $_pdo-&gt;query($sql); 


    if ( !$stat ) 
    {
	$err = $_pdo-&gt;errorInfo();
        trigger_error(&quot;Query Failed&lt;BR&gt;&quot;.$sql.&quot;&lt;br&gt;&quot; . 
              &quot;Error &quot;.$_pdo-&gt;errorCode(). &quot; - &quot;. 
              $err[2], E_USER_ERROR); 
              return; 
    } 
</pre></code></p>
<p>The above code firstly extracts the user's entered date range as a pair of dates. We then create a temporary table called <strong>t_lateness</strong> that will hold the number of days each loan was taken out for. We then extract into this table the number of days each loan was taken out for for loans returned between the entered date range.</p><hr />
   <a name="tut4_1_sql"></a><h3 class="title">Enter the report SQL</h3><p>Now select the <strong>Query Details</strong> tab and enter the following SQL text against the SQL parameter.
<code><pre>
SELECT days_loaned days_loaned, count(*) frequency 
FROM t_lateness 
WHERE 1 = 1   
AND days_loaned &gt; 1 GROUP BY 1 
ORDER BY  days_loaned 
</pre></code>
On pressing <strong>Ok</strong>, you will see an error message complaining that the table <strong>t_lateness</strong> does not exist. This is because this table will have been created when the custom PHP code entered above executes before this main query. Therefore you can ignore this warning.</p>
<p>Now return to report execution mode. Notice that the date range criteria is defaulted to last month. Press the <strong>Execute</strong> button. You will see a count for each number of days films have been loaned out for.</p><hr />
   <a name="tut3_1_graph"></a><h3 class="title">Create the graph</h3><p>Now we want to represent these counts as a pie chart. Return to design mode and select the <strong>Output</strong> tab. 
Since graphs need to be plotted against a group we need to set up a group which represents the whole of the report. This means the graph can be plotted at the end. Select the <strong>Groups</strong> button, click on the <strong>Add</strong> button and select the resulting Group link.</p>
<p>Now change the parameter <strong>Group On Column</strong> to <strong>REPORT_BODY</strong>.</p>
<p>Now add the graph by selecting the <strong>Graphs</strong> sub-tab and clicking the <strong>Add</strong> button. Select the resulting <strong>Graphs 0</strong> button and set the following parameters :-</p>
   <p><table border = "1"><colgroup span = "1"/><thead><td></td>
  <td>Parameter</td>
  <td>Value</td>
  <td>Comment</td></thead>
<tbody><tr><td>1.</td>
  <td>Group Column</td>
  <td>REPORT_BODY</td>
  <td></td></tr>
<tr><td>2.</td>
  <td>Title</td>
  <td>Late Return Summary</td>
  <td>The title to give the report</td></tr>
<tr><td>3.</td>
  <td>Column for X Labels</td>
  <td>days_loaned</td>
  <td>Indicates that the month will be used to label the X Axis</td></tr></tbody></table>
Press the <strong>Ok</strong> button to continue.</p><hr />
   <a name="tut4_1_plots"></a><h3 class="title">Enter the Graph Plot Values</h3><p>The last graph configuration you need to do is to indicate which report column contains the value to be plotted. Click on the <strong>Plots</strong> subtab, press the <strong>Add</strong> button and press the resulting <strong>Plots</strong> link. You should be presented with a set of plot parameters.</p>
   <p>Change these as follows :-
<table border = "1"><colgroup span = "1"/><thead><td></td>
  <td>Parameter</td>
  <td>Value</td>
  <td>Comment</td></thead>
<tbody><tr><td>1.</td>
  <td>Column To Plot</td>
  <td>frequency</td>
  <td>The monthly rental count will be plotted above each month.</td></tr>
<tr><td>2.</td>
  <td>Plot Style</td>
  <td>PIE</td>
  <td>The value will be plotted as bar chart</td></tr></tbody></table>
Remember to press the <strong>Ok</strong> button.</p><hr />
   <a name="tut4_check"></a><h3 class="title">Check the Report</h3><p>Return to report execution mode and run the report.
You should see the pie chart at the end of the report.</p><hr /></span>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="33%" align="left" valign="top"><a href="../reportico/tutorial_reportico.tutorial3_1.pkg.html">Prev</a></td>
<td width="34%" align="center" valign="top"><a href=
"../reportico/tutorial_reportico.howto.pkg.html">Up</a></td>
<td width="33%" align="right" valign="top"><a href=
"../reportico/tutorial_reportico.appendix1.pkg.html">Next</a></td>
</tr>

<tr>
<td width="33%" align="left" valign="top">Tutorial 3 - The Monthly Returns Report</td>
<td width="34%" align="center" valign="top">Reportico Tutorial</td>
<td width="33%" align="right" valign="top">Appendix 1 - Reportico Project Configuration parameters</td>
</tr>
</table>
        <div class="credit">
		    <hr />
		    Documentation generated on Mon, 09 Jun 2014 18:10:08 +0100 by <a href="http://www.phpdoc.org">phpDocumentor 1.4.3</a>
	      </div>
      </td></tr></table>
    </td>
  </tr>
</table>

</body>
</html>